package com.mbc.rmt.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import com.mbc.common.dao.DAO;
import com.mbc.rmt.data.CashPaymentByReturnDataList;
import com.mbc.rmt.entities.CashPaymentByReturn;
import com.mbc.rmt.entities.CashPaymentByReturnRpt;

public class CashPaymentByReturnDAOImpls extends DAO<CashPaymentByReturn> implements CashPaymentByReturnDAO{

	public final String field_slip_id = "slipid";
	public final String field_slid_no = "slipno";
	public final String field_slid_date = "slipdate";
	public final String field_created_date = "createddate";
	public final String field_modified_date = "modifieddate";
	public final String field_defaultbranch_id = "defaultbranchid";
	public final String field_created_by = "createdby";
	public final String field_modified_by = "modifiedby";
	public final String field_type_id = "typeid";
	public final String field_type_code = "typecode";
	public final String field_returnslip_id = "returnslipid";
	public final String field_return_no = "returnno";
	public final String field_agent_id = "agentid";
	public final String field_agent_code = "agentcode";
	public final String field_addcode_id = "addcodeid";
	public final String field_addcode = "addcode";
	public final String field_receiptno = "receiptno";
	public final String field_sender = "sender";
	public final String field_receiver = "receiver";
	public final String field_nrc = "nrc";
	public final String field_address = "address";
	public final String field_currency_id = "currencyid";
	public final String field_currencyrate_id = "currencyrateid";
	public final String field_currencyrate_code = "currencyratecode";
	public final String field_rate = "rate";
	public final String field_amount = "amount";
	public final String field_extra_fees = "extrafees";
	public final String field_bank_id = "bankid";
	public final String field_bank_code = "bankcode";
	public final String field_definedetail_id = "definedetailid";
	public final String field_returndetial_id = "returndetailid";
	
	public final String field_paidamount = "paidamount";
	public final String field_preturnstatus = "p_returnstatus";
	
	public CashPaymentByReturnDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "CashPaymentByReturn";
	}
	
	enum TRAN_MODE { SAVE, UPDATE }
	
	@Override
	public void create(CashPaymentByReturn entity) throws SQLException {
		query = insertParam(table_name, new String[] { field_slip_id,field_slid_no,
					field_slid_date,field_created_date,field_modified_date,field_created_by,field_modified_by,
					field_defaultbranch_id,field_type_id,field_type_code,field_returnslip_id,
					field_return_no,field_agent_id,field_agent_code, field_addcode_id,field_addcode,
					field_receiptno,field_sender,field_receiver,field_nrc,field_address,field_currency_id,field_rate,
					field_amount,field_extra_fees,field_bank_id,field_bank_code,field_returndetial_id,field_definedetail_id });		
		pstmt = conn.prepareStatement(query);
		updateRecord(entity, TRAN_MODE.SAVE);
		pstmt.executeUpdate();
		pstmt.close();		
	}

	@Override
	public CashPaymentByReturn find(long key) throws SQLException {
		CashPaymentByReturn entity = null;
		query = getSelectQuery() + " where slipid = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			entity = readRecord(rs);
		rs.close();
		pstmt.close();
		return entity;
	}

	@Override
	public void update(CashPaymentByReturn entity) throws SQLException {
		query = updateParam(table_name, new String[] { field_slip_id,field_slid_no,
				    field_slid_date,field_modified_date, field_modified_by,field_defaultbranch_id,
				    field_type_id,field_type_code,field_returnslip_id,field_return_no,field_agent_id,
				    field_agent_code, field_addcode_id,field_addcode,field_receiptno,field_sender,
				    field_receiver,field_nrc,field_address,field_currency_id,field_rate,
				    field_amount,field_extra_fees,field_bank_id,field_bank_code,field_returndetial_id,field_definedetail_id  }) + " AND " + field_slip_id + " = " + entity.getSlipid()	;		
		pstmt = conn.prepareStatement(query);
		updateRecord(entity, TRAN_MODE.UPDATE);
		pstmt.executeUpdate();
		pstmt.close();	
		
	}

	@Override
	public void delete(CashPaymentByReturn entity) throws SQLException {
		query = "DELETE FROM "+table_name+" WHERE "+field_slip_id+" = "+entity.getSlipid();
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
		
	}

	@Override
	public CashPaymentByReturn getCashPaymentByReturn(CashPaymentByReturn entity)
			throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public CashPaymentByReturnDataList findCashPaymentByReturn(CashPaymentByReturn cri)
			throws SQLException {
		CashPaymentByReturnDataList l_DataList = new CashPaymentByReturnDataList();
		ArrayList<CashPaymentByReturn> l_entityList = new ArrayList<CashPaymentByReturn>();
		String l_cri = "";
		
		if(!cri.getFieldname().equals("SlipDate"))
			l_cri += "and cpr." + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%'";
		else
			l_cri += "and (Convert(varchar(100), cpr." + cri.getFieldname() + ", 103)) like N'%" + cri.getKeyword() + "%'";
		
		l_cri += " and defaultbranchid =" + cri.getBranchId();
		
		if(!cri.isTyping())
			query = " DECLARE @homecurrencyid bigint "+
					    " SET @homecurrencyid=(SELECT HomeCurrencyid FROM Setup)"+
						"set rowcount "
					+ cri.getSize()
					+ " select * from (select  row_number() over(order by cpr.slipno desc) as [rowno], " +
					   "cpr.*, ISNULL(cr.currencyrateid,0) currencyrateid,(CASE WHEN cr.currencyratecode IS NULL AND @homecurrencyid=cpr.currencyid"+
					   " THEN c.currencycode+c.currencycode ELSE cr.currencyratecode END )currencyratecode from "+table_name+" cpr "+
					   " INNER JOIN Currency c ON cpr.currencyid=c.currencyid"+ 
					   " LEFT JOIN CurrencyRate cr ON cr.SourceCurrencyid = cpr.currencyid"+ 
					   "  AND cr.convertcurrencyid=@homecurrencyid and cr.branchid = "+cri.getBranchid()
				    + " where 1 = 1 " + l_cri + ") " + table_name
				    + " where rowno > " + cri.getIndex();
		else
			query =  " DECLARE @homecurrencyid bigint "+
				         " SET @homecurrencyid=(SELECT HomeCurrencyid FROM Setup)"+
				         " select  " +
				         "cpr.*, ISNULL(cr.currencyrateid,0) currencyrateid,(CASE WHEN cr.currencyratecode IS NULL AND @homecurrencyid=cpr.currencyid"+
				         " THEN c.currencycode+c.currencycode ELSE cr.currencyratecode END )currencyratecode from "+table_name+" cpr "+
				         " INNER JOIN Currency c ON cpr.currencyid=c.currencyid"+ 
				         " LEFT JOIN CurrencyRate cr ON cr.SourceCurrencyid = cpr.currencyid"+ 
				         "  AND cr.convertcurrencyid=@homecurrencyid and cr.branchid = "+cri.getBranchid()+
					     " where 1=1 "+
						 " and defaultbranchid =" + cri.getBranchId()+
						 " and cpr.slipno = '"+cri.getSlipno()+"'";	 
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
				l_entityList.add(readRecords(rs));
			} while (rs.next());
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();		
		if(!cri.isTyping())
		query = "select count(*) from "+table_name+" cpr "+
				" LEFT JOIN CurrencyRate cr ON cr.SourceCurrencyid = cpr.CurrencyID AND" +
				" cr.convertcurrencyid=(SELECT HomeCurrencyid FROM Setup) where 1 = 1"
				   + l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	@Override
	public CashPaymentByReturnDataList findCashReturnDetaillist(
			CashPaymentByReturn cri) throws SQLException {
		// TODO Auto-generated method stub
		CashPaymentByReturnDataList l_DataList = new CashPaymentByReturnDataList();
				ArrayList<CashPaymentByReturn> l_entityList = new ArrayList<CashPaymentByReturn>();
				String l_cri = " and defaultbranchid =" + cri.getBranchId()+
								    " and crd.CurrencyID =" + cri.getCurrencyid()+
									" and trtype = "+cri.getTrtype();
				if(cri.getFieldname().equals("SlipNo"))
					l_cri+=" and(SlipNo like N'%" + cri.getKeyword() + "%')" ;
				else
			        l_cri+=" and(crd."+cri.getFieldname()+" like N'%" + cri.getKeyword() + "%')" ;
				if(!cri.isTyping())
					query = "set rowcount "
							+ cri.getSize()
							+ " select * from (select  row_number() over(order by cr.slipid) as [rowno], " +
							" cr.SlipID returnslipid,crd.SlipDetailID returndetailid,crd.DefineDetailID,cr.AgentID,dptd.AddCodeId, cr.SlipNo returnno,a.agent_code agentcode,ac.Code addcode,crd.ReceiptNo,crd.Sender,crd.Receiver,crd.Address,dptd.NRC,crd.Amount,dptd.ExtraFees" +
							" from CashReturn cr " +
							" inner join CashReturnDetails crd on cr.SlipID = crd.SlipID" +
							" inner join DefinePaymentTypeDetail dptd on dptd.DetailID = crd.DefineDetailID" +
							" left join Agent a on a.agent_id = cr.AgentID" +
							" inner join AddCode ac on ac.AddCodeId = dptd.AddCodeID"
							+ " where 1 = 1 and crd.Amount <> isnull(crd.paidamount,0) " + l_cri + ") " + table_name
							+ " where rowno > " + cri.getIndex();
				else{
					query =  "select  " +
							" cr.SlipID returnslipid,crd.SlipDetailID returndetailid,crd.DefineDetailID,cr.AgentID,dptd.AddCodeId, cr.SlipNo returnno,a.agent_code agentcode,ac.Code addcode,crd.ReceiptNo,crd.Sender,crd.Receiver,crd.Address,dptd.NRC,crd.Amount,dptd.ExtraFees" +
							" from CashReturn cr " +
							" inner join CashReturnDetails crd on cr.SlipID = crd.SlipID" +
							" inner join DefinePaymentTypeDetail dptd on dptd.DetailID = crd.DefineDetailID" +
							" left join Agent a on a.agent_id = cr.AgentID" +
							" inner join AddCode ac on ac.AddCodeId = dptd.AddCodeID"
							+ " where 1 = 1 and crd.Amount <> isnull(crd.paidamount,0)  and defaultbranchid =" + cri.getBranchId()+
							   " and crd.CurrencyID =" + cri.getCurrencyid()+
							   " and trtype = "+cri.getTrtype();
					   if(!cri.getSlipno().equals(""))
							  query =  " and cr.slipno = '"+cri.getSlipno()+"'";
				}
				pstmt = conn.prepareStatement(query);
				rs = pstmt.executeQuery();
				if (rs.next()) {
					do {
						l_entityList.add(readRecordForReturn(rs));
					} while (rs.next());
				}
				l_DataList.setEntityList(l_entityList);
				rs.close();
				pstmt.close();	
				if(!cri.isTyping()){
					query = "select count(cr.slipid) from CashReturn cr"+
								" inner join CashReturnDetails crd on cr.SlipID = crd.SlipID"+
								" left join type t on t.type_id = cr.TypeID  where 1 = 1"
							   + l_cri;
					pstmt = conn.prepareStatement(query);
					rs = pstmt.executeQuery();
					if (rs.next()) {
						l_DataList.setTotalRecord(rs.getLong(1));
					}
				}
				rs.close();
				pstmt.close();
				return l_DataList;
	}
	
	protected CashPaymentByReturn readRecordForReturn(ResultSet rs) throws SQLException {
		CashPaymentByReturn entity = new CashPaymentByReturn();
		entity.setReturnslipid(rs.getLong(field_returnslip_id));
		entity.setReturndetailid(rs.getLong(field_returndetial_id));
		entity.setDefinedetailid(rs.getLong(field_definedetail_id));
		entity.setAgentid(rs.getLong(field_agent_id));
		entity.setAddcodeid(rs.getLong(field_addcode_id));
		entity.setReturnslipno(rs.getString(field_return_no));
		entity.setAgentcode(rs.getString(field_agent_code));
		entity.setAddcode(rs.getString(field_addcode));
		entity.setReceiptno(rs.getString(field_receiptno));
		entity.setSender(rs.getString(field_sender));
		entity.setReceiver(rs.getString(field_receiver));
		entity.setAddress(rs.getString(field_address));
		entity.setNrc(rs.getString(field_nrc));
		entity.setAmount(rs.getDouble(field_amount));
		entity.setExtrafees(rs.getDouble(field_extra_fees));
		return entity;
	}
	
	@Override
	protected CashPaymentByReturn readRecord(ResultSet rs) throws SQLException {
		CashPaymentByReturn entity = new CashPaymentByReturn();
		entity.setSlipid(rs.getLong(field_slip_id));
		entity.setSlipno(rs.getString(field_slid_no));
		entity.setSlipdate(rs.getDate(field_slid_date));
		entity.setCreateddate(rs.getDate(field_created_date));
		entity.setModifieddate(rs.getDate(field_modified_date));
		entity.setCreatedby(rs.getLong(field_created_by));
		entity.setModifiedby(rs.getLong(field_modified_by));
		entity.setDefinedetailid(rs.getLong(field_defaultbranch_id));
		entity.setTypeid(rs.getLong(field_type_id));
		entity.setTypecode(rs.getString(field_type_code));
		entity.setReturnslipid(rs.getLong(field_returnslip_id));
		entity.setReturnslipno(rs.getString(field_return_no));
		entity.setAgentid(rs.getLong(field_agent_id));
		entity.setAgentcode(rs.getString(field_agent_code));
		entity.setAddcodeid(rs.getLong(field_addcode_id));
		entity.setAddcode(rs.getString(field_addcode));
		entity.setReceiptno(rs.getString(field_receiptno));
		entity.setSender(rs.getString(field_sender));
		entity.setReceiver(rs.getString(field_receiver));
		entity.setNrc(rs.getString(field_nrc));
		entity.setAddress(rs.getString(field_address));
		entity.setCurrencyid(rs.getLong(field_currency_id));
		entity.setRate(rs.getDouble(field_rate));
		entity.setAmount(rs.getDouble(field_amount));
		entity.setExtrafees(rs.getDouble(field_extra_fees));
		entity.setBankid(rs.getLong(field_bank_id));
		entity.setBankcode(rs.getString(field_bank_code));
		entity.setDefinedetailid(rs.getLong(field_definedetail_id));
		entity.setReturndetailid(rs.getLong(field_returndetial_id));
		return entity;
	}
	
	protected CashPaymentByReturn readRecords(ResultSet rs) throws SQLException {
		CashPaymentByReturn entity = new CashPaymentByReturn();
		entity.setSlipid(rs.getLong(field_slip_id));
		entity.setSlipno(rs.getString(field_slid_no));
		entity.setSlipdate(rs.getDate(field_slid_date));
		entity.setCreateddate(rs.getDate(field_created_date));
		entity.setModifieddate(rs.getDate(field_modified_date));
		entity.setCreatedby(rs.getLong(field_created_by));
		entity.setModifiedby(rs.getLong(field_modified_by));
		entity.setDefinedetailid(rs.getLong(field_defaultbranch_id));
		entity.setTypeid(rs.getLong(field_type_id));
		entity.setTypecode(rs.getString(field_type_code));
		entity.setReturnslipid(rs.getLong(field_returnslip_id));
		entity.setReturnslipno(rs.getString(field_return_no));
		entity.setAgentid(rs.getLong(field_agent_id));
		entity.setAgentcode(rs.getString(field_agent_code));
		entity.setAddcodeid(rs.getLong(field_addcode_id));
		entity.setAddcode(rs.getString(field_addcode));
		entity.setReceiptno(rs.getString(field_receiptno));
		entity.setSender(rs.getString(field_sender));
		entity.setReceiver(rs.getString(field_receiver));
		entity.setNrc(rs.getString(field_nrc));
		entity.setAddress(rs.getString(field_address));
		entity.setCurrencyid(rs.getLong(field_currency_id));
		entity.setRate(rs.getDouble(field_rate));
		entity.setAmount(rs.getDouble(field_amount));
		entity.setExtrafees(rs.getDouble(field_extra_fees));
		entity.setBankid(rs.getLong(field_bank_id));
		entity.setBankcode(rs.getString(field_bank_code));
		entity.setReturndetailid(rs.getLong(field_returndetial_id));
		entity.setDefinedetailid(rs.getLong(field_definedetail_id));
		entity.setCurrencyratecode(rs.getString(field_currencyrate_code));
		entity.setCurrencyrateid(rs.getLong(field_currencyrate_id));
		return entity;
	}
	
	protected void updateRecord(CashPaymentByReturn entity, TRAN_MODE mode) throws SQLException {
		int i = 1;
		pstmt.setLong(i++, entity.getSlipid());
		pstmt.setString(i++, entity.getSlipno());
		pstmt.setTimestamp(i++, new Timestamp(entity.getSlipdate().getTime()));
		if(mode == TRAN_MODE.SAVE){
			pstmt.setTimestamp(i++, new Timestamp(entity.getCreateddate().getTime()));
			pstmt.setTimestamp(i++, new Timestamp(entity.getModifieddate().getTime()));
			pstmt.setLong(i++, entity.getCreatedby());
			pstmt.setLong(i++, entity.getModifiedby());
		}
		else{
			pstmt.setTimestamp(i++, new Timestamp(entity.getModifieddate().getTime()));
			pstmt.setLong(i++, entity.getModifiedby());
		}
		pstmt.setLong(i++, entity.getBranchid());
		pstmt.setLong(i++, entity.getTypeid());
		pstmt.setString(i++, entity.getTypecode());
		pstmt.setLong(i++, entity.getReturnslipid());
		pstmt.setString(i++, entity.getReturnslipno());
		if(entity.getAgentid()!=null)
			pstmt.setLong(i++, entity.getAgentid());
		else
			pstmt.setString(i++, null);
		pstmt.setString(i++, entity.getAgentcode());
		pstmt.setLong(i++, entity.getAddcodeid());
		pstmt.setString(i++, entity.getAddcode());
		pstmt.setString(i++, entity.getReceiptno());
		pstmt.setString(i++, entity.getSender());
		pstmt.setString(i++, entity.getReceiver());
		pstmt.setString(i++, entity.getNrc());
		pstmt.setString(i++, entity.getAddress());
		pstmt.setLong(i++, entity.getCurrencyid());
		pstmt.setDouble(i++, entity.getRate());
		pstmt.setDouble(i++, entity.getAmount());
		pstmt.setDouble(i++, entity.getExtrafees());
		if(entity.getBankid()!=null)
			pstmt.setLong(i++, entity.getBankid());
		else
			pstmt.setString(i++, null);
		pstmt.setString(i++, entity.getBankcode());
		pstmt.setLong(i++, entity.getReturndetailid());
		pstmt.setLong(i++, entity.getDefinedetailid());
	}

	@Override
	public CashPaymentByReturn findpaidamt(long key) throws SQLException {
		CashPaymentByReturn entity = new CashPaymentByReturn();
		query = "select isnull(paidamount,0)paidamount  from CashReturnDetails where slipdetailid = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			entity.setPaidamount(rs.getDouble(field_paidamount));
		rs.close();
		pstmt.close();
		return entity;
	}

	@Override
	public void updateCashReturn(CashPaymentByReturn info)
			throws SQLException {
		query = updateParam("CashReturnDetails", new String[] { field_paidamount,field_preturnstatus}) +
				" AND slipdetailid  = " + info.getReturndetailid();
		
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setDouble(i++, info.getPaidamount());
		pstmt.setByte(i++,info.getP_returnstatus());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public ResultSet prepareCashPaymentByReturnReport(
			CashPaymentByReturnRpt info) throws SQLException {
		// TODO Auto-generated method stub
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_CashPaymentByReturnRpt ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		prepareCashPaymentByReturnReporttParameter(l_pstmt, info);
		l_rs = l_pstmt.executeQuery();
		return l_rs;
	}
	
	private static void prepareCashPaymentByReturnReporttParameter(CallableStatement l_pstmt,
			CashPaymentByReturnRpt  info) throws SQLException {
		int i = 1;

		l_pstmt.setString(i++, (new java.sql.Timestamp(info.getFromdate().getTime())).toString());
		l_pstmt.setString(i++, (new java.sql.Timestamp(info.getTodate().getTime())).toString());
		l_pstmt.setString(i++, info.getFromSlipID());
		l_pstmt.setString(i++, info.getToSlipID());
		l_pstmt.setString(i++, info.getFromReturnNo());
		l_pstmt.setString(i++, info.getToReturnNo());
		l_pstmt.setString(i++, info.getFromTypeID());
		l_pstmt.setString(i++, info.getToTypeID());		
		l_pstmt.setString(i++, info.getFromCurrencyID());
		l_pstmt.setString(i++, info.getToCurrencyID());
		l_pstmt.setString(i++, info.getFromBankCheque());
		l_pstmt.setString(i++, info.getToBankCheque());
		l_pstmt.setString(i++, info.getFromUserID());
		l_pstmt.setString(i++, info.getToUserID());
		l_pstmt.setString(i++, info.getGroupby());
		l_pstmt.setLong(i++, info.getBranchid());		
	}
}
